﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace GenerateDiagram
{
    public class SqlTable : AbstractTable
    {
        public override string GenerateClass(string nameSpace, Common.Version version, bool canRenderForeignKey)
        {
            string classPatternPath = Common.GetPatternFileWithVersion(version);

            IOHelper ioHelper = new IOHelper();
            string classData = ioHelper.ReadFile(classPatternPath);

            string classBody = "";
           
            foreach (var item in this.Columns)
            {
                if (item.RenderOption.CanRender)
                    classBody +=  "\r\n\t" + item.ToProperty(version);
            }

            if (canRenderForeignKey && this.ForeignKeys.Count>0)
            {
                classBody += "\r\n\n\t//Foreign key---------------------------------";
                foreach (var item in this.ForeignKeys)
                {
                    classBody += "\r\n\t" + item.ToForeignKeyProperty(version);
                }
            }
            classData += "\r\n";
            classData = string.Format(classData, nameSpace, this.ClassName, StaticClass.EntityBaseClassName + "<" + this.ClassName + ">", classBody);
            return classData.Replace("@", "{").Replace("$", "}");
        }


        public override void SetColumns(string databaseName)
        {
            string commandText = "SELECT" +
                " (TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME) as id," +
                " CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as isNullable," +
                " ORDINAL_POSITION         as colid," +
                " COLUMN_NAME              as name," +
                " c.DATA_TYPE              as dataType," +
                " CHARACTER_MAXIMUM_LENGTH as length, " +
                " ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION) AS prec," +
                " NUMERIC_SCALE            as scale," +
                " COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as isIdentity" +
                " FROM INFORMATION_SCHEMA.COLUMNS c" +
                " WHERE c.Table_Name='" + this.Name + "'";
            DataTable myDataTable = DataConnection.Instance.ExecuteReader(commandText, databaseName);
            Columns = new List<AbstractColumn>();
            foreach (DataRow myDataRow in myDataTable.Rows)
            {
                AbstractColumn column = new SqlColumn
                    {
                        ID = Convert.ToInt32(myDataRow["colid"]),
                        Name = Convert.ToString(myDataRow["name"]),
                        IsNullable = Convert.ToBoolean(myDataRow["isNullable"]),
                        IsIdentity = Convert.ToBoolean(myDataRow["isIdentity"]),
                        RenderOption = new RenderOption { CanRender = true },

                        ColumnType = new SqlColumnType
                        {
                            //Length = myDataRow["length"] == null ? 0 : Convert.ToInt32(myDataRow["length"]),
                            DataType = Convert.ToString(myDataRow["dataType"])
                        }
                    };
                this.Columns.Add(column);
            }
            //Set Pk
             SetPrimaryKey(databaseName);

            //Load foreignkey
            LoadForeignKey(databaseName);
        }

        private void SetPrimaryKey(string databaseName)
        {
            string str = "SELECT" +
            " (k.TABLE_CATALOG + '.' + k.TABLE_SCHEMA + '.' + k.TABLE_NAME) as id," +
            " k.CONSTRAINT_NAME                                             as name," +
            " k.COLUMN_NAME                                                 as colname," +
            " k.ORDINAL_POSITION                                            as colid" +
            " FROM" +
            " INFORMATION_SCHEMA.KEY_COLUMN_USAGE k" +
            " JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.CONSTRAINT_NAME = c.CONSTRAINT_NAME" +
            " WHERE c.CONSTRAINT_TYPE='PRIMARY KEY' AND c.TABLE_NAME='" + this.Name + "'";

            var rows = DataConnection.Instance.ExecuteReader(str, databaseName).Rows;
            foreach (DataRow item in rows)
            {
                var s = item["colname"].ToString();
                var col = Columns.Find(c => c.Name == item["colname"].ToString());
                if (col != null)
                    col.IsPrimaryKey = (Convert.ToInt32(item["colid"]) >= 0);
            }
        }

        private void LoadForeignKey(string databaseName)
        {
            this.ForeignKeys = new List<ForeignKey>();
            string commandText = "SELECT" +
                " rc.CONSTRAINT_NAME  as Name, " +
                " fk.TABLE_CATALOG + '.' + fk.TABLE_SCHEMA + '.' + fk.TABLE_NAME as ThisTable, " +
                " fk.COLUMN_NAME      as ThisColumn," +
                " pk.TABLE_CATALOG + '.' + pk.TABLE_SCHEMA + '.' + pk.TABLE_NAME as OtherTable," +
                " pk.COLUMN_NAME      as OtherColumn," +
                " cu.ORDINAL_POSITION as Ordinal" +
                " FROM" +
                " INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc" +
                " JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk" +
                " ON" +
                " rc.CONSTRAINT_CATALOG        = fk.CONSTRAINT_CATALOG AND" +
                " rc.CONSTRAINT_NAME           = fk.CONSTRAINT_NAME" +
                " JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk" +
                " ON" +
                " rc.UNIQUE_CONSTRAINT_CATALOG = pk.CONSTRAINT_CATALOG AND" +
                " rc.UNIQUE_CONSTRAINT_NAME    = pk.CONSTRAINT_NAME" +
                " JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu" +
                " ON" +
                " rc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME" +
                " WHERE fk.Table_name='" + this.Name + "'" +
                " ORDER BY" +
                " ThisTable," +
                " Ordinal";
           var rows = DataConnection.Instance.ExecuteReader(commandText, databaseName).Rows;
            foreach (DataRow item in rows)
            {
                var name = Convert.ToString(item["Name"]);
                var thisTable = Convert.ToString(item["ThisTable"]);
                var otherTable = Convert.ToString(item["OtherTable"]);
                var thisColumnName = Convert.ToString(item["ThisColumn"]);
                var otherColumnName = Convert.ToString(item["OtherColumn"]);

                var foreignKey = new ForeignKey
                {
                    OtherTable = otherTable.Substring(otherTable.LastIndexOf('.') + 1),
                    ThisTable = thisTable.Substring(thisTable.LastIndexOf('.') + 1),
                    RenderOption = new RenderOption { CanRender = true },
                    MemberName = name,
                    CanBeNull = false,
                    BackReference = null,
                    Name = thisColumnName
                };

                this.ForeignKeys.Add(foreignKey);
            }
        }

    }
}
